package DBTasks;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class PetSearch extends DBTask {
    private static String createDogAtShelterView = "CREATE OR REPLACE VIEW PetsAtShelters AS SELECT * From Pet "
            + "NATURAL JOIN Dog JOIN Shelter ON Shelter.Name=Pet.Shelter_Name WHERE Zip_Code=? AND Pet.Pet_ID NOT IN (SELECT Pet_ID FROM Adopts)";
    private static String creatCatAtShelterView = "CREATE OR REPLACE VIEW PetsAtShelters AS SELECT * From Pet "
            + "NATURAL JOIN Cat JOIN Shelter ON Shelter.Name=Pet.Shelter_Name WHERE Zip_Code=? AND Pet.Pet_ID NOT IN (SELECT Pet_ID FROM Adopts)";
    private static String selectMatchingPetsQuery = "SELECT Pet_ID, Pet_Name, Shelter_Name, CONCAT(Street_City_State,', ',Zip_Code) as Address, Phone"
            + " FROM PetsAtShelters WHERE Breed = ? AND Age = ? AND Gender = ?";

    public static ArrayList<Pet> search(String petType, String breed, int zipcode, String gender, String age) {
        if (con == null) {
            connectToDatabase();
        }

        PreparedStatement createView, matchingPetsQuery;
        ArrayList<Pet> matchingPets = new ArrayList<Pet>();
        try {
            if (petType.equals("Dog")) {
                createView = con.prepareStatement(createDogAtShelterView);
            }
            else {
                createView = con.prepareStatement(creatCatAtShelterView);
            }

            createView.setInt(1, zipcode);
            createView.executeUpdate();

            matchingPetsQuery = con.prepareStatement(selectMatchingPetsQuery);
            matchingPetsQuery.setString(1, breed);
            matchingPetsQuery.setString(2, age);
            matchingPetsQuery.setString(3, gender);
            matchingPetsQuery.execute();
            ResultSet rs = matchingPetsQuery.getResultSet();
            while (rs.next()) {
                String petName = rs.getString("Pet_Name");
                String shelterName = rs.getString("Shelter_Name");
                String address = rs.getString("Address");
                String phoneNumber = rs.getString("Phone");
                int petId = rs.getInt("Pet_ID");
                matchingPets.add(new Pet(shelterName, phoneNumber, address, petId, petName, petType));
            }

            createView.executeUpdate("DROP VIEW PetsAtShelters");
            return matchingPets;
        }
        catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
